Create database iCMS
--Drop database iCMS
go
use iCMS
go
Create table [Permission]
(
	[permission_id] int identity(1,1) primary key,
	[permission_name] nvarchar(50),
)
go
create table [User]
(
	[user_id] int identity(1,1) Primary key,
	[user_name] nvarchar(50),
	[password] nvarchar(50),
	[full_name] nvarchar(50),
	[birthday] datetime,
	[sex] bit,
	[address] nvarchar(255),
	[phone] nvarchar(50),
	[permission_id] int foreign key references [Permission]([permission_id]),
	[date_added] datetime default Getdate()
)
go
create table [Category]
(
	category_id int identity(1,1) primary key,
	category_name nvarchar(50),
	date_added datetime default getdate()
)
go
create table [Topic]
(
	topic_id int identity(1,1) primary key,
	topic_name nvarchar(50),
	topic_des nvarchar(255),
	date_added datetime default Getdate()
)
go
create table [Article]
(
	article_id int identity(1,1) primary key,
	[user_id] int foreign key references [User]([user_id]),
	article_name nvarchar(50),
	article_sumary nvarchar(255),
	article_des text,
	[image] nvarchar(50),
	category_id int foreign key references [Category](category_id),
	topic_id int foreign key references [Topic](topic_id),
	[date_added] datetime default Getdate()
)
go
create table [Comment]
(
	comment_id int identity(1,1) primary key,
	article_id int foreign key references [Article](article_id),
	comment_name nvarchar(50),
	comment_des text,
	email nvarchar(50),
	[status] bit,
	[date_added] datetime default Getdate()
)
-- CREATE PROC INSERT COMMENT
Create proc IS_Comment
@comment_id int = null,
@article_id int = null,
@comment_name nvarchar(50) = null,
@comment_des text = null,
@email nvarchar(50) = null,
@status bit = 'false'
AS
	BEGIN
		DECLARE @row int
		DECLARE @err int
		BEGIN TRANSACTION
			UPDATE
				Comment
			set
				article_id = @article_id,
				comment_name = @comment_name,
				comment_des = @comment_des,
				[status] = @status
			where
				comment_id = @comment_id
				OR email = @email
			SELECT @row = @@ROWCOUNT , @err = @@ERROR
			IF(@row = 0)
				BEGIN
					INSERT INTO
						Comment
					Values
					(
						@article_id,
						@comment_name,
						@comment_des,
						@email,
						@status,
						GETDATE()
					) 
				END
				ELSE IF @err <> 0
				BEGIN
					ROLLBACK TRANSACTION
					RETURN
				END
		COMMIT TRANSACTION
			
	END
Create proc SL_Comment
@comment_id int  = null,
@article_id int  = null,
@email nvarchar(50) = null
AS
	BEGIN
		IF(@comment_id IS NULL AND @email IS NULL AND @article_id IS NULL)
			BEGIN
				SELECT 
				*
				FROM
				Comment
			END
		ELSE IF @article_id IS NOT NULL AND (@comment_id IS NULL AND @email IS NULL)
			BEGIN
			SELECT 
			*
			FROM 
			Comment
			WHERE 
			article_id = @article_id
			END
		ELSE IF @article_id IS NULL
			BEGIN
			SELECT
			*
			FROM 
			Comment
			WHERE
			comment_id = @comment_id
			OR email = @email	
			END
	END

--CREATE PROC SELECT ARTICLE
CREATE PROC sp_SelectArticle

@article_id int = null,
@user_id int = null,
@article_name nvarchar(50)= null,
@category_id int = null,
@topic_id int = null
AS
BEGIN
	IF(@article_id IS NULL AND @user_id IS NULL AND @article_name IS NULL AND @category_id IS NULL AND @topic_id IS NULL)
		BEGIN
			SELECT * FROM Article
		END
		ELSE IF @article_id IS NOT NULL AND(@user_id IS NULL AND @article_name IS NULL AND @category_id IS NULL AND @topic_id IS NULL)
			BEGIN
				SELECT * FROM Article WHERE article_id =@article_id
			END
		ELSE IF @user_id IS NOT NULL AND(@article_id IS NULL AND @article_name IS NULL AND @category_id IS NULL AND @topic_id IS NULL )
			BEGIN
				SELECT * FROM Article WHERE [user_id] =@user_id
			END
		ELSE IF @article_name IS NOT NULL AND (@article_id IS NULL AND @user_id IS NULL AND @category_id IS NULL AND @topic_id IS NULL)
			BEGIN
				SELECT * FROM Article WHERE article_name =@article_name
			END
		ELSE IF @category_id IS NOT NULL AND(@article_id IS NULL AND @user_id IS NULL AND @article_name IS NULL AND @topic_id IS NULL)
			BEGIN
				SELECT * FROM Article WHERE category_id =@category_id
			END
		ELSE IF @topic_id IS NOT NULL AND(@article_id IS NULL AND @user_id IS NULL AND @article_name IS NULL AND @category_id IS NULL)
			BEGIN
				SELECT * FROM Article WHERE topic_id =@topic_id
			END
END

--CREATE PROC INSERT ARTICLE
CREATE PROC [dbo].[sp_InsertArticle]

@article_id int = null,
@user_id int = null,
@article_name nvarchar(50)=null,
@article_sumary nvarchar(225)=null,
@article_des text=null,
@image nvarchar(50)=null,
@category_id int = null,
@topic_id int = null

AS
	BEGIN
		DECLARE @row int
		DECLARE @err int
		BEGIN TRANSACTION
			UPDATE 
				Article
			SET 
				[user_id] = @user_id,
				article_name =@article_name,
				article_sumary = @article_sumary,
				article_des = @article_des,
				[image] = @image,
				category_id = @category_id,
				topic_id = @topic_id
			WHERE
				article_id=@article_id
			SELECT @row = @@ROWCOUNT, @err = @@ERROR
			IF(@row = 0)
				BEGIN
					INSERT INTO
						Article
					VALUES
						(
						@user_id,
						@article_name,
						@article_sumary,
						@article_des,
						@image,
						@category_id,
						@topic_id,
						GETDATE()
						)
				END
				ELSE IF @err <> 0
				BEGIN
					ROLLBACK TRANSACTION
					RETURN
				END
				COMMIT TRANSACTION
END
			 
